package com.gzhryc.dictionary.services;


import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;

import com.gzhryc.common.StringTools;
import com.gzhryc.common.data.TreeUtils;
import com.gzhryc.common.excel.ExcelTools;
import com.gzhryc.common.exceptions.LogicException;
import com.gzhryc.common.json.JsonTools;
import com.gzhryc.common.logging.Logger;
import com.gzhryc.dictionary.dao.CountryAreaDao;
import com.gzhryc.dictionary.dao.db.Country;
import com.gzhryc.dictionary.dao.db.CountryArea;
import com.gzhryc.dictionary.dao.enums.CountryAreaEnum;
import com.gzhryc.dictionary.services.dto.CountryAreaData;
import com.gzhryc.dictionary.services.dto.CountryAreaSearch;
import com.gzhryc.jdbc.Conditions;
import com.gzhryc.jdbc.MultiDBTools;

public class CountryAreaService {

    static Logger log = Logger.getLogger(CountryAreaService.class);

    static final Integer DEFAULT_PRIORITY = 10;

    public static CountryAreaService self() {
        return MultiDBTools.getService(CountryAreaService.class, true);
    }

    public static CountryAreaService self(String jdbcKey) {
        return MultiDBTools.getService(jdbcKey, CountryAreaService.class, true);
    }

    CountryAreaDao countryAreaDao;

    public CountryAreaService(String jdbcKey) {
        countryAreaDao = new CountryAreaDao(jdbcKey);
    }

    public List<CountryArea> findBySearch(CountryAreaSearch search, Integer pageNo, Integer pageLimit) {
        Conditions condition = countryAreaDao.buildConditions(search);
        condition.asc("priority");
        condition.asc("id");
        try {
            return countryAreaDao.findByCondition(condition, pageNo, pageLimit);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Long countBySearch(CountryAreaSearch search) {
        Conditions condition = countryAreaDao.buildConditions(search);
        try {
            return countryAreaDao.count(condition);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }

    public String[] findChinaAreaNames(String[] ids){
        String[] result = null;
        if(ids != null && ids.length > 0) {
            Country china = CountryService.self().getChina();
            if (china != null) {
                Conditions condition = new Conditions(CountryArea.class);
                condition.and().is("countryId", china.getId());
                condition.and().in("id", ids);

                try {
                    List<CountryArea> areaList = countryAreaDao.findByCondition(condition);
                    if(areaList != null && areaList.size() > 0){
                        result = new String[ids.length];
                        for(int i=0;i< ids.length;i++){
                            if(StringTools.isNumber(ids[i])) {
                                Long id = Long.valueOf(ids[i]);
                                for (CountryArea area : areaList) {
                                    if (area.getId().equals(id)){
                                        result[i] = area.getName();
                                        break;
                                    }
                                }
                            }
                        }
                    }
                } catch (SQLException e) {
                    log.error(e.getMessage(),e);
                }
            }
        }
        return result;
    }

    public List<CountryArea> findTree(Long countryId) {
        if (countryId != null) {
            Conditions condition = new Conditions(CountryArea.class);
            condition.and().is("countryId", countryId);
            condition.asc("priority");
            condition.asc("id");
            try {
                List<CountryArea> data = countryAreaDao.findByCondition(condition);
                if (data != null) {
                    return TreeUtils.byTree(data);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<CountryArea> findChinaTree() {
        Country china = CountryService.self().getChina();
        if (china != null) {
            Conditions condition = new Conditions(CountryArea.class);
            condition.and().is("countryId", china.getId());
            condition.asc("priority");
            condition.asc("id");
            try {
                List<CountryArea> data = countryAreaDao.findByCondition(condition);
                if (data != null) {
                    return TreeUtils.byTree(data);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public CountryArea getByCountryIdAndName(Long countryId, String name) {
        if (countryId != null && StringUtils.isNotBlank(name)) {
            Conditions condition = new Conditions(CountryArea.class);
            condition.and().is("countryId", countryId);
            condition.and().is("name", name);
            try {
                return countryAreaDao.get(condition);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public CountryArea getByCountryIdAndName(Long countryId, String name, Integer type) {
        if (countryId != null && StringUtils.isNotBlank(name) && type != null) {
            Conditions condition = new Conditions(CountryArea.class);
            condition.and().is("countryId", countryId);
            condition.and().is("type", type);
            condition.and().likeBoth("name", name);

            try {
                return countryAreaDao.get(condition);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public CountryArea getByParentIdAndFullName(Long parentId, String fullName) {
        if (parentId != null && StringUtils.isNotBlank(fullName)) {
            Conditions condition = new Conditions(CountryArea.class);
            condition.and().is("parentId", parentId);
            condition.and().is("fullName", fullName);
            try {
                return countryAreaDao.get(condition);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public CountryArea getByCode(String code) {
        if (StringUtils.isNotBlank(code)) {
            Conditions condition = new Conditions(CountryArea.class);
            condition.and().is("code", code);
            try {
                return countryAreaDao.get(condition);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public CountryArea getById(Long id) {
        try {
            return countryAreaDao.getById(id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取所有省
     *
     * @return
     */
    public List<CountryArea> findAllProvince(Long countryId) {
        Conditions condition = new Conditions(CountryArea.class);
        condition.and().is("countryId", countryId);
        condition.and().is("type", CountryAreaEnum.Type.Province.index());
        condition.asc("priority");
        condition.asc("id");
        try {
            return countryAreaDao.findByCondition(condition);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取所有子节点
     *
     * @param parentId
     * @return
     */
    public List<CountryArea> findChildByParentId(Long parentId) {
        Conditions condition = new Conditions(CountryArea.class);
        condition.and().is("parentId", parentId);
        condition.asc("priority");
        condition.asc("id");
        try {
            return countryAreaDao.findByCondition(condition);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<CountryArea> findChildByParentId(Long parentId, Integer type) {
        Conditions condition = new Conditions(CountryArea.class);
        condition.and().is("parentId", parentId);
        condition.and().is("type", type);
        condition.asc("priority");
        condition.asc("id");
        try {
            return countryAreaDao.findByCondition(condition);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 新增地区
     *
     * @param chinaArea
     * @return
     * @throws LogicException
     */
    public synchronized boolean add(CountryArea chinaArea) throws LogicException {
        CountryArea temp = getByParentIdAndFullName(chinaArea.getParentId(), chinaArea.getFullName());
        if (temp != null) {
            throw new LogicException("名称重复，数据：" + chinaArea.getName() + ",PID：" + chinaArea.getParentId());
        }
        chinaArea.setCreateDate(new Date());
        if (chinaArea.getStatus() == null) {
            chinaArea.setStatus(CountryAreaEnum.Status.Usable.index());
        }
        if (chinaArea.getPriority() == null) {
            chinaArea.setPriority(10);
        }
        try {
            return countryAreaDao.insert(chinaArea) > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public synchronized boolean updateNotNull(CountryArea chinaArea) throws LogicException {
        CountryArea temp = getByParentIdAndFullName(chinaArea.getParentId(), chinaArea.getFullName());
        if (temp != null && !temp.getId().equals(chinaArea.getId())) {
            throw new LogicException("名称重复，数据：" + chinaArea.getName() + ",PID：" + chinaArea.getParentId());
        }
        try {
            return countryAreaDao.updateNotNull(chinaArea) > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean deleteById(Long id) {
        try {
            return countryAreaDao.deleteById(id) > 0;
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        return false;
    }

    /**
     * @Description: 根据国家id删除地区id
     * @Param: [countryId]
     * @return: boolean true 成功 false 失败
     * @Author: lyd 2020/10/14
     */
    public boolean deleteByCountryId(Long countryId) {
        if (countryId != null) {
            try {
                return countryAreaDao.deleteByCountryId(countryId) > 0;
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        }
        return false;
    }

    public String getCountryAreaStr() {
        Country country = CountryService.self().getByEnglishName("China");
        if (country != null) {
            List<Map<String, String>> provinceData = new ArrayList<Map<String, String>>();
            List<List<Map<String, String>>> cityData = new ArrayList<List<Map<String, String>>>();
            List<List<Map<String, String>>> areaData = new ArrayList<List<Map<String, String>>>();
            List<CountryArea> provinceList = CountryAreaService.self().findAllProvince(country.getId());
            if (provinceList != null && provinceList.size() > 0) {
                for (CountryArea province : provinceList) {
                    Map<String, String> provinceItem = new HashMap<String, String>();
                    provinceItem.put("label", province.getName());
                    provinceItem.put("value", province.getId().toString());
                    provinceData.add(provinceItem);
                    List<CountryArea> cityList = CountryAreaService.self().findChildByParentId(province.getId(), CountryAreaEnum.Type.City.index());
                    List<Map<String, String>> cityItemData = new ArrayList<Map<String, String>>();
                    if (cityList != null && cityList.size() > 0) {
                        for (CountryArea city : cityList) {
                            Map<String, String> cityItem = new HashMap<String, String>();
                            cityItem.put("label", city.getName());
                            cityItem.put("value", city.getId().toString());
                            cityItemData.add(cityItem);

                            List<CountryArea> areaList = CountryAreaService.self().findChildByParentId(city.getId(), CountryAreaEnum.Type.CityArea.index());
                            List<Map<String, String>> cityAreaItemData = new ArrayList<Map<String, String>>();
                            if (areaList != null && areaList.size() > 0) {
                                for (CountryArea area : areaList) {
                                    Map<String, String> areaItem = new HashMap<String, String>();
                                    areaItem.put("label", area.getName());
                                    areaItem.put("value", area.getId().toString());
                                    cityAreaItemData.add(areaItem);
                                }
                                areaData.add(cityAreaItemData);
                            }
                        }
                        cityData.add(cityItemData);
                    }

                }
            }
            CountryAreaData data = new CountryAreaData();
            data.setProvinceData(provinceData);
            data.setCityData(cityData);
            data.setAreaData(areaData);
            return JsonTools.toJson(data);

        }
        return null;
    }

    /**
     * 导入Excel数据
     * @param country
     * @param file
     * @return
     */
    public boolean importExcelData(Country country, File file){
        try {
            Workbook workbook = ExcelTools.readExcelToWorkbook(file.getName(), new FileInputStream(file));
            List<CountryArea> areaList = ExcelTools.readExcel(workbook, CountryArea.class,
                    new String[]{"id", "countryId", "name", "fullName", "pinyin", "code", "type", "typeName", "priority", "status", "statusName"
                            , "leftNum", "rightNum", "rootId", "parentId"},
                    0, 1);
            int i = 0;
            if (areaList != null && areaList.size() > 0) {
                //记录插入失败的记录
                List<CountryArea> failList = new ArrayList<CountryArea>();
                for (CountryArea area : areaList) {
                    area.setCountryId(country.getId());
                    CountryArea temp = getById(area.getId());
                    if (temp == null) {
                        if (!add(area)) {
                            failList.add(area);
                            continue;
                        }
                    } else {
                        if (!updateNotNull(area)) {
                            failList.add(area);
                            continue;
                        }
                    }
                    i++;
                }
                //将由于parentId 造成插入失败的记录 重新插入
                if (failList.size() > 0) {
                    for (CountryArea area : failList) {
                        if (area.getCountryId() == null)
                            area.setCountryId(country.getId());
                        CountryArea temp = getById(area.getId());
                        if (temp == null) {
                            if (!add(area)) {
                                log.error("添加id" + area.getId() + "时错误");
                                continue;
                            }
                        } else {
                            if (!updateNotNull(area)) {
                                log.error("修改id" + area.getId() + "时错误");
                                continue;
                            }
                        }
                        i++;
                    }
                }
                return true;
            }
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } catch (LogicException e) {
            log.error(e.getMessage(), e);
        }
        return false;
    }
}
